<?php
/**
 * Excel 数据导入
 *
 */

namespace excel\Excel;

use PHPExcel_Reader_Excel2007;
use PHPExcel_Reader_Excel5;
use PHPExcel;
use think\Exception;

class Excel {

    public static function download($name, $list) {
        $objPHPExcel = new PHPExcel();

        $objPHPExcel->setActiveSheetIndex(0);            //设置第一个内置表（一个xls文件里可以有多个表）为活动的

        //得到当前活动的表,注意下文教程中会经常用到$objActSheet
        $objActSheet = $objPHPExcel->getActiveSheet();
        // 位置bbb  *为下文代码位置提供锚
        // 给当前活动的表设置名称
        $objActSheet->setTitle($name);

        foreach($list as $key => $item) {
            foreach ($item as $key2 => $item2) {
                $objActSheet->setCellValueExplicit(
                    Excel::getCellIndex($key2 + 1, $key + 1),
                    is_array($item2) ? $item2['data'] : $item2,
                    \PHPExcel_Cell_DataType::TYPE_STRING
                );
            }
        }

        header('Content-Type: application/vnd.ms-excel; charset=UTF-8');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=UTF-8');
        header("Content-Disposition: attachment;filename=\"$name".".xlsx\"");

        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory:: createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save( 'php://output');
        exit();
    }

    // 获取 Excel 数据
    public static function get($file, $fieldConfig = array()) {

        //实例化PHPExcel类
        $PHPExcel = new PHPExcel();
        //默认用excel2007读取excel，若格式不对，则用之前的版本进行读取
        $PHPReader = new PHPExcel_Reader_Excel2007();
        if (!$PHPReader->canRead($file)) {
            $PHPReader = new PHPExcel_Reader_Excel5();
            if (!$PHPReader->canRead($file)) {
                throw new Exception('no Excel');
            }
        }

        //读取Excel文件
        $PHPExcel = $PHPReader->load($file);
        $cols = explode(',', 'A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK,AL,AM,AN,AO,AP,AQ,AR,AS,AT');
        //读取excel文件中的第一个工作表
        $sheets = $PHPExcel->getAllSheets();
        $excelData = array();
        foreach ($sheets as $index => $sheet) {
            $PHPExcel->setActiveSheetIndex($index);
            // 获取sheet名称
            $title = $sheet->getTitle();

            //取得最大的列号
            $maxColumn = $sheet->getHighestColumn();
            //取得最大的行号
            $maxRow = $sheet->getHighestRow();

            // 获取列之间的对应关系
            $colConfig = array();
            for($i = 0; $i < count($cols); $i++) {
                $col = $cols[$i];
                $colTitle = $PHPExcel->getActiveSheet()->getCell($col . 1)->getValue();

                $tagName = array_search($colTitle, $fieldConfig);

                if(!$tagName) continue;

                $colConfig[$col] = $tagName;
                if($col == $maxColumn) break;
            }

            if(!$colConfig) continue;

            $sheetData = array();
            //从第二行开始读取数据,第一行是列名
            for ($currentRow = 2; $currentRow <= $maxRow; $currentRow++) {
                // 获取数据
                $data = [];
                for($i = 0; $i < count($cols); $i++) {
                    $col = $cols[$i];
                    if(!isset($colConfig[$col])) continue;

                    $filed = $colConfig[$col];

                    $val = $PHPExcel->getActiveSheet()->getCell($cols{$i} . $currentRow)->getValue();
                    $val = trim($val);

                    $data[$filed] = $val;
                    if($col == $maxColumn) break;
                }

                if(!$data) continue;

                $sheetData[] = $data;
            }

            if(!$sheetData) continue;

            $excelData[$index] = $sheetData;
        }

        return $excelData;
    }

    public static function getCellIndex($column, $row) {
        return static::getCellColumnIndex($column).$row;
    }

    public static function getCellColumnIndex($column) {
        $baseLetter = explode(',', 'A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z');
        if($column > 26) {
            return static::getCellColumnIndex(floor($column/26)).$baseLetter[$column%26 - 1];
        } else {
            return $baseLetter[$column - 1];
        }
    }
}